#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import time, sys
sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
#from charts.models import *

import datetime
from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()




#从settings.ini获取任务类型编号
#settings = Ini('../apps/settings.ini')
def run():
    #本脚本从任务书表取数据，按照任务所处阶段统计任务、子任务各阶段正常、异常及完成情况

    req = db.metadata.tables['assignment']                
    req_kfzx = db.metadata.tables['kfzx']
    #获取本月月初和月末时间
    month_first_day,month_last_day,quarter_view,quarter = get_quarter()

    #判断本月末是否是季度末
    quarter = long(quarter)
    if(quarter <> 3) and (quarter <> 6) and (quarter <> 9) and (quarter <> 12):
        sys.exit()


    #zc统计状态为正常的各阶段阶段下任务书数据量
    #zc = select([req.c.type,req.c.stage,func.count(req.c.ass_id)],and_(req.c.status == '1',req.c.type.in_('1','2'))).group_by(req.c.type,req.c.stage)
#    zc = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.type,req.c.stage,req.c.kfzx],
#                and_(req.c.status == '1',
#                    ~req.c.stage.in_(['FN']),
#                     req.c.type == '3')
#                ).group_by(req.c.type,req.c.kfzx,req.c.stage)

    condition = select([func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))],
        and_(req.c.status.in_(['2','4','5']),
        ~req.c.stage.in_(['FN']),
        req.c.type.in_(['3'])))
    
    zc = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.type,req.c.stage,req.c.kfzx],
                and_(req.c.status == '1',
                ~req.c.stage.in_(['FN']),
                req.c.type.in_(['3']),
                ~func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')).in_(condition)
                     )
                ).group_by(req.c.type,req.c.kfzx,req.c.stage)
    
    #yc统计状态为延迟、异常终止、暂停的各阶段阶段下任务书数据量
    #yc = select([req.c.type,func.count(req.c.ass_id)],and_(req.c.status.in_('2','4','5'),req.c.type.in_('1','2'))).group_by(req.c.type,req.c.stage)
    yc = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.type,req.c.stage,req.c.kfzx],
                and_(req.c.status.in_(['2','4','5']),
                     ~req.c.stage.in_(['FN']),
                     req.c.type == '3',
                     #req.c.modified_date<=month_last_day,
                     req.c.modified_date>=month_first_day)
                ).group_by(req.c.type,req.c.kfzx,req.c.stage)

    #wc统计状态为完成阶段阶段下本月任务书数据量
    #wc = select([req.c.type,func.count(req.c.ass_id)],and_(req.c.modified_date<month_last_day,req_mx.c.modified_date>=month_first_day,req.c.stage == '18',req.c.type.in_('1','2'))).group_by(req.c.type)
    wc = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.type,req.c.kfzx],
                and_(#req.c.modified_date<=month_last_day,
                     req.c.end_date>=month_first_day,
                     #req.c.modified_date>=month_first_day,
                     req.c.stage == 'FN',
                     req.c.type == '3')
                ).group_by(req.c.type,req.c.kfzx)
    print zc,yc,wc
    #print yc
    zc_rs = zc.execute()
    yc_rs = yc.execute()
    wc_rs = wc.execute()

    sql_kfzx = select([req_kfzx.c.id])
    kfzx_rs = list(sql_kfzx.execute())

    #定义字典型data
    data = {}

    #定义两个字典，一个是阶段，一个是任务类型
    types = ['3']
    stages = ['total_num','gys_num_zc','gys_num_yc','ss_num_zc','ss_num_yc','yans_num_zc','yans_num_yc','kffk_num_zc','kffk_num_yc','wc_num_zc']


    #初始化data字典   
    for x in types:
        data[x] = {}
        for row in kfzx_rs:
            zx, = row
            kfzx = zx
            #print kfzx
            zx = str(zx)
            data[x][zx] = {}
            for y in stages:
                data[x][zx][y] = 0
            data[x][zx]['month_date'] = month_last_day
            data[x][zx]['quarter'] = quarter_view
            data[x][zx]['type'] = x
            data[x][zx]['kfzx'] = kfzx


    #data['total_num'] = 0
        
    total_num = 0
    #以下两个for语句完成new_rs，old_rs中新增和续建准开发需求放在一条记录中                                                            
        
    for row in zc_rs:
        row_num,row_type,row_stage,kfzx = row
        #data[row_type]['kfzx'] = kfzx
        kfzx = str(kfzx)
        #print 'aa',row_type,row_stage,row_num
        if row_stage =='31':
            data[row_type][kfzx]['gys_num_zc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='32':
            data[row_type][kfzx]['ss_num_zc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='33':
            data[row_type][kfzx]['yans_num_zc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='34':
            data[row_type][kfzx]['yans_num_zc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='FN':
            data[row_type][kfzx]['kffk_num_zc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num

    for row in yc_rs:
        row_num,row_type,row_stage,kfzx = row
        #data[row_type][kfzx]['kfzx'] = kfzx
        kfzx = str(kfzx)
        #print 'bb',row_type,row_num,row_stage
        if row_stage =='31':
            data[row_type][kfzx]['gys_num_yc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='32':
            data[row_type][kfzx]['ss_num_yc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='33':
            data[row_type][kfzx]['yans_num_yc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='34':
            data[row_type][kfzx]['yans_num_yc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num
        elif row_stage =='FN':
            data[row_type][kfzx]['kffk_num_yc'] = row_num
            data[row_type][kfzx]['total_num'] += row_num

    for row in wc_rs:
        row_num,row_type,kfzx = row
        #data[row_type]['kfzx'] = kfzx
        kfzx = str(kfzx)
        #print 'cc',row_type,row_num
        data[row_type][kfzx]['wc_num_zc'] = row_num
        data[row_type][kfzx]['total_num'] += row_num

        

            
    print data
    #将本月准开发需求汇总数据赋值给total_num


    req_model = get_model('assignment_quarter_report_stage_fxm')

    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
    req_model.remove(req_model.c.month_date == month_last_day)
    #print data
    # 按照任务类型存储统计数据
    for t,v in data.items():
    #    print 't',t
    #    print 'v',v
        for x,y in v.items():
            try:
                r = req_model(**y)
                r.save()
            except:
#                print 'xxxxxxxxxxxxxxxxxxxxxxxxxx',x,'y',y
                raise


if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise
